package com.power.poi.excel;

/**
 * @author sunyu
 */

import com.power.common.util.StringUtil;
import com.power.poi.annotation.Excel;
import com.power.poi.excel.model.PopulateValParams;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;


public class ExcelUtil<T> {
    private static final String EXCEPTION_MSG = " is unmarked by ExcelAnnotion annotation";
    private static final String NULL_MSG = " The collection of List is null";
    private static final String DATE_FORMAT = "yyyy-MM-dd";
    private static final String FONT_NAME = "宋体";
    private static final String SHEET_NAME_ERROR = "(This Sheet name is not exist in your specify excel)";
    private static final String HEADER_CONTENT_ERROR = "Header content can not be null";
    private static final String NULL_WORKBOOK = "Excel workbook is null";
    private static final int SHEET_SIZE = 6500;
    Class<T> clazz;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * this method could auto Merged a header area
     *
     * @param list          datas
     * @param sheetName
     * @param headerContent content of header
     * @return
     */
    public InputStream generateExcel(List<T> list, String sheetName, String headerContent) {
        HSSFWorkbook workbook = null;
        if (null == list) {
            throw new NullPointerException(NULL_MSG);
        }
        if (StringUtil.isEmpty(headerContent)) {
            throw new NullPointerException(HEADER_CONTENT_ERROR);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle titleStyle = ExcelExportStyle.getTitleStyle(workbook, true);
            // set font style of title
            Font titleFont = ExcelExportFontStyle.getTitleFont(workbook);
            titleStyle.setFont(titleFont);
            // create style of header
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            // create style of header font
            Font headerFont = ExcelExportFontStyle.getTitleFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            // set default size of a sheet
            int sheetSize = SHEET_SIZE;

            // Take out a total of how much a sheet
            int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);

            PopulateValParams params = new PopulateValParams();
            params.setBodyStyle(bodyStyle);
            params.setHeaderStyle(headerStyle);
            params.setTitleStyle(titleStyle);
            params.setFields(fields);
            params.setSheetSize(sheetSize);
            params.setHeaderContent(headerContent);
            params.setList(list);
            params.setSheetNo(sheetNo);
            params.setWorkbook(workbook);
            params.setSheetName(sheetName);
            populateValue(params);
        }
        return ExcelPubUtil.getInputStream(workbook);
    }

    private void populateValue(PopulateValParams params) {

        Workbook workbook = params.getWorkbook();
        List<Field> fields = params.getFields();
        List<T> list = params.getList();
        for (int index = 0; index < params.getSheetNo(); index++) {
            // generate Excel sheet
            Sheet sheet = workbook.createSheet();
            // set Excel sheet name
            workbook.setSheetName(index, params.getSheetName() + (index + 1));
            Row row;

            row = sheet.createRow(0);
            // set header
            ExcelPubUtil.setHeader(params.getHeaderStyle(), sheet, row, fields.size() - 1,
                    params.getHeaderContent());
            // create area of header
            row = sheet.createRow(1);
            // set content of header
            ExcelPubUtil.setTitle(fields, params.getTitleStyle(), sheet, row);

            int startNo = index * params.getSheetSize();
            int endNo = Math.min(startNo + params.getSheetSize(), list.size());
            // Write each record, each record corresponds to a row in the
            // excel
            // table
            for (int i = startNo; i < endNo; i++) {
                row = sheet.createRow(i + 2 - startNo);
                // obtain export object
                T t = list.get(i);
                // set body
                ExcelPubUtil.setBody(fields, t, params.getBodyStyle(), row);
            }
        }
    }

    /**
     * if you use struts framework ,you could use this method you must put list
     * of data,name of excel sheet and file
     *
     * @param list
     * @param sheetName
     * @param file
     * @return
     * @throws IOException
     * @throws NullPointerException
     */
//	public InputStream generateExcel(List<T> list, String sheetName) {
//		HSSFWorkbook workbook = null;
//		if (null == list) {
//			throw new NullPointerException(NULL_MSG);
//		} else {
//			workbook = new HSSFWorkbook();
//			// set style of title
//			HSSFCellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
//			// set font style of title
//			HSSFFont headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
//			headerStyle.setFont(headerFont);
//			// get the fields that marked by ExcelAnnotation
//			List<Field> fields = ExcelReflectUtil.getFields(clazz);
//			if (fields.size() <= 0) {
//				throw new RuntimeException(clazz + EXCEPTION_MSG);
//			}
//			// create body style
//			HSSFCellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook,true);
//			// set default size of a sheet
//			int sheetSize = SHEET_SIZE;
//
//			// Take out a total of how much a sheet
//			int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);
//
//			for (int index = 0; index < sheetNo; index++) {
//				// generate Excel sheet
//				HSSFSheet sheet = workbook.createSheet();
//				// set Excel sheet name
//				workbook.setSheetName(index, sheetName + (index + 1));
//				HSSFRow row;
//				// create area of header
//				row = sheet.createRow(0);
//				// set content of header
//				ExcelPubUtil.setTitle(fields, headerStyle, sheet, row);
//
//				int startNo = index * sheetSize;
//				int endNo = Math.min(startNo + sheetSize, list.size());
//				// Write each record, each record corresponds to a row in the
//				// excel
//				// table
//				for (int i = startNo; i < endNo; i++) {
//					row = sheet.createRow(i + 1 - startNo);
//					// obtain export object
//					T t = (T) list.get(i);
//					// set body
//					ExcelPubUtil.setBody(fields, t, bodyStyle, row);
//				}
//			}
//
//		}
//		return ExcelPubUtil.getInputStream(workbook);
//	}

    /**
     * Used for servlet or spring mvc
     * resp.setContentType("application/vnd.ms-excel;charset=utf-8");
     * resp.setHeader("Content-Disposition", "attachment;filename=" + new
     * String("video.xls".getBytes(), "iso-8859-1")); ServletOutputStream os =
     * resp.getOutputStream();
     *
     * input sheetName ,ServletOutputStream,dataList
     *
     * @param sheetName
     * @param out
     * @param list
     * @return
     */
//	public boolean exportExcel(String sheetName, OutputStream out, List<T> list) {
//		HSSFWorkbook workbook = null;
//		if (null == list) {
//			throw new NullPointerException(NULL_MSG);
//		} else {
//			workbook = new HSSFWorkbook();
//			// set style of title
//			HSSFCellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
//			// set font style of title
//			HSSFFont headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
//			headerStyle.setFont(headerFont);
//			// get the fields that marked by ExcelAnnotation
//			List<Field> fields = ExcelReflectUtil.getFields(clazz);
//			if (fields.size() <= 0) {
//				throw new RuntimeException(clazz + EXCEPTION_MSG);
//			}
//			// create body style
//			HSSFCellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook,true);
//			// set default size of a sheet
//			int sheetSize = SHEET_SIZE;
//
//			// Take out a total of how much a sheet
//			int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);
//
//			for (int index = 0; index < sheetNo; index++) {
//				// generate Excel sheet
//				HSSFSheet sheet = workbook.createSheet();
//				// set Excel sheet name
//				workbook.setSheetName(index, sheetName + (index + 1));
//				HSSFRow row;
//				// create area of title
//				row = sheet.createRow(0);
//				// set content of title
//				ExcelPubUtil.setTitle(fields, headerStyle, sheet, row);
//
//				int startNo = index * sheetSize;
//				int endNo = Math.min(startNo + sheetSize, list.size());
//				// Write each record, each record corresponds to a row in the
//				// excel
//				// table
//				for (int i = startNo; i < endNo; i++) {
//					row = sheet.createRow(i + 1 - startNo);
//					// obtain export object
//					T t = (T) list.get(i);
//					// set body
//					ExcelPubUtil.setBody(fields, t, bodyStyle, row);
//				}
//			}
//
//		}
//		return ExcelPubUtil.getInputStreamIsSuccess(workbook,out);
//	}

    /**
     * Used for servlet or spring mvc
     * resp.setContentType("application/vnd.ms-excel;charset=utf-8");
     * resp.setHeader("Content-Disposition", "attachment;filename=" + new
     * String("video.xls".getBytes(), "iso-8859-1")); ServletOutputStream os =
     * resp.getOutputStream();
     * <p>
     * sheetName,headerContent,ServletOutputStream,dataList
     *
     * @param sheetName     工作表名称
     * @param headerContent 标题头
     * @param out           输出流
     * @param list          导出数据
     * @return
     */
    public boolean exportExcelWithHeader(String sheetName,
                                         String headerContent, OutputStream out, List<T> list) {
        HSSFWorkbook workbook = null;
        if (null == list) {
            throw new NullPointerException(NULL_MSG);
        }
        if (StringUtil.isEmpty(headerContent)) {
            throw new NullPointerException(HEADER_CONTENT_ERROR);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle titleStyle = ExcelExportStyle.getTitleStyle(workbook, true);
            // set font style of title
            Font titleFont = ExcelExportFontStyle.getTitleFont(workbook);
            titleStyle.setFont(titleFont);
            // create style of header
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            // create style of header font
            Font headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            // set default size of a sheet
            int sheetSize = SHEET_SIZE;

            // Take out a total of how much a sheet
            int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);

            for (int index = 0; index < sheetNo; index++) {
                // generate Excel sheet
                HSSFSheet sheet = workbook.createSheet();
                // set Excel sheet name
                workbook.setSheetName(index, sheetName + (index + 1));
                HSSFRow row;

                row = sheet.createRow(0);
                // set header
                ExcelPubUtil.setHeader(headerStyle, sheet, row, fields.size() - 1,
                        headerContent);
                // create area of header
                row = sheet.createRow(1);
                // set content of header
                ExcelPubUtil.setTitle(fields, titleStyle, sheet, row);

                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // Write each record, each record corresponds to a row in the
                // excel
                // table
                for (int i = startNo; i < endNo; i++) {
                    row = sheet.createRow(i + 2 - startNo);
                    // obtain export object
                    T t = list.get(i);
                    // set body
                    ExcelPubUtil.setBody(fields, t, bodyStyle, row);
                }
            }
        }
        return ExcelPubUtil.getInputStreamIsSuccess(workbook, out);
    }

    /**
     * @param sheetName     sheet name
     * @param headerContent header
     * @param note          note
     * @param out           out
     * @param list          list
     * @return
     */
    public boolean exportExcelWithHeaderAndNote(String sheetName,
                                                String headerContent, String note, OutputStream out, List<T> list) {
        HSSFWorkbook workbook = null;
        if (null == list) {
            throw new NullPointerException(NULL_MSG);
        }
        if (StringUtil.isEmpty(headerContent)) {
            throw new NullPointerException(HEADER_CONTENT_ERROR);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle titleStyle = ExcelExportStyle.getTitleStyle(workbook, true);
            // set font style of title
            Font titleFont = ExcelExportFontStyle.getTitleFont(workbook);
            titleStyle.setFont(titleFont);
            // create style of header
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            CellStyle noteStyle = ExcelExportStyle.createNoteStyle(workbook);
            // create style of header font
            Font headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            // set default size of a sheet
            int sheetSize = SHEET_SIZE;

            // Take out a total of how much a sheet
            int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);

            for (int index = 0; index < sheetNo; index++) {
                // generate Excel sheet
                HSSFSheet sheet = workbook.createSheet();
                // set Excel sheet name
                workbook.setSheetName(index, sheetName + (index + 1));
                HSSFRow row;

                row = sheet.createRow(0);
                // set header
                ExcelPubUtil.setHeader(headerStyle, sheet, row, fields.size() - 1,
                        headerContent);

                // create note
                row = sheet.createRow(1);
                this.annotate(noteStyle, sheet, row, fields.size() - 1, note);
                // create area of header
                row = sheet.createRow(2);
                // set content of header
                ExcelPubUtil.setTitle(fields, titleStyle, sheet, row);

                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // Write each record, each record corresponds to a row in the
                // excel
                // table
                for (int i = startNo; i < endNo; i++) {
                    row = sheet.createRow(i + 3 - startNo);
                    // obtain export object
                    T t = list.get(i);
                    // set body
                    ExcelPubUtil.setBody(fields, t, bodyStyle, row);
                }
            }
        }
        return ExcelPubUtil.getInputStreamIsSuccess(workbook, out);
    }

    /**
     * @param sheetName     sheetName
     * @param headerContent header
     * @param note          note
     * @param out           OutputStream
     * @param map           map
     * @param sumContent    sumContent
     * @return
     */
    public boolean exportExcelWithHeaderAndNote(String sheetName,
                                                String headerContent, String note, OutputStream out,
                                                Map<String, List<T>> map, String sumContent) {
        HSSFWorkbook workbook = null;
        if (null == map) {
            throw new NullPointerException(NULL_MSG);
        }
        if (StringUtil.isEmpty(headerContent)) {
            throw new NullPointerException(HEADER_CONTENT_ERROR);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle titleStyle = ExcelExportStyle.getTitleStyle(workbook, true);
            // set font style of title
            Font titleFont = ExcelExportFontStyle.getTitleFont(workbook);
            titleStyle.setFont(titleFont);
            // create style of header
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            CellStyle noteStyle = ExcelExportStyle.createNoteStyle(workbook);
            // create style of header font
            Font headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            for (Map.Entry<String, List<T>> entry : map.entrySet()) {
                // generate Excel sheet
                HSSFSheet sheet = workbook.createSheet(entry.getKey());
                HSSFRow row;

                row = sheet.createRow(0);

                // set header
                ExcelPubUtil.setHeader(headerStyle, sheet, row, fields.size() - 1,
                        headerContent);
                // create note
                row = sheet.createRow(1);
                this.annotate(noteStyle, sheet, row, fields.size() - 1, note);
                // create area of header
                row = sheet.createRow(2);
                // set content of header
                ExcelPubUtil.setTitle(fields, titleStyle, sheet, row);
                // get value from map
                List<T> list = entry.getValue();
                int size = list.size();
                for (int i = 0; i < size; i++) {
                    row = sheet.createRow(i + 3);
                    row.setHeightInPoints(25);
                    // obtain export object
                    T t = list.get(i);
                    // set body
                    ExcelPubUtil.setBody(fields, t, bodyStyle, row);
                }
                row = sheet.createRow(size + 3);
                this.annotate(headerStyle, sheet, row, fields.size() - 1, sumContent);
                row = sheet.createRow(size + 4);
                this.setTotal(fields, bodyStyle, row, 3, size + 4);
            }
        }
        return ExcelPubUtil.getInputStreamIsSuccess(workbook, out);
    }


    /**
     * you must put list of data,name of excel sheet and file
     *
     * @param list
     * @param sheetName
     * @param file
     * @return boolean
     */
    public boolean exportExcel(List<T> list, String sheetName, File file) {
        HSSFWorkbook workbook = null;
        if (null == list) {
            throw new NullPointerException(NULL_MSG);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            // set font style of title
            Font headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            // set default size of a sheet
            int sheetSize = SHEET_SIZE;
            // Take out a total of how much a sheet.
            int sheetNo = ExcelPubUtil.totalSheet(list.size(), sheetSize);
            for (int index = 0; index <= sheetNo; index++) {
                // generate Excel sheet
                HSSFSheet sheet = workbook.createSheet();
                // set Excel sheet name
                workbook.setSheetName(index, sheetName + (index + 1));
                HSSFRow row;
                // create area of title
                row = sheet.createRow(0);
                // set content of title
                ExcelPubUtil.setTitle(fields, headerStyle, sheet, row);

                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // Write each record, each record corresponds to a row in the
                // excel
                // table
                for (int i = startNo; i < endNo; i++) {
                    row = sheet.createRow(i + 1 - startNo);
                    // obtain export object
                    T t =  list.get(i);
                    // set body
                    ExcelPubUtil.setBody(fields, t, bodyStyle, row);
                }
            }
        }
        return ExcelPubUtil.getInputStreamIsSuccess(workbook, file);
    }

    public boolean exportExcel(Map<String, List<T>> map, File file) {
        boolean flag = false;
        Workbook workbook = null;
        if (null == map) {
            throw new NullPointerException(NULL_MSG);
        } else {
            workbook = new HSSFWorkbook();
            // set style of title
            CellStyle headerStyle = ExcelExportStyle.getHeaderStyle(workbook);
            // set font style of title
            Font headerFont = ExcelExportFontStyle.getHeaderFont(workbook);
            headerStyle.setFont(headerFont);
            // get the fields that marked by ExcelAnnotation
            List<Field> fields = ExcelReflectUtil.getFields(clazz);
            if (fields.size() <= 0) {
                throw new RuntimeException(clazz + EXCEPTION_MSG);
            }
            // create body style
            CellStyle bodyStyle = ExcelExportStyle.getBodyStyle(workbook, true);
            for (Map.Entry<String, List<T>> entry : map.entrySet()) {
                // generate Excel sheet
                Sheet sheet = workbook.createSheet(entry.getKey());
                // set Excel sheet name
                // workbook.setSheetName(index,entry.getKey());
                Row row;
                // create area of title
                row = sheet.createRow(0);
                // set content of title
                ExcelPubUtil.setTitle(fields, headerStyle, sheet, row);

                // Write each record, each record corresponds to a row in the
                // excel
                // table
                List<T> list = entry.getValue();
                for (int i = 0; i < list.size(); i++) {
                    row = sheet.createRow(i + 1);
                    row.setHeightInPoints(20);
                    // obtain export object
                    T t = list.get(i);
                    // set body
                    ExcelPubUtil.setBody(fields, t, bodyStyle, row);
                }
                //row = sheet.createRow(list.size()+1);//创建合计行
                //this.annotate(bodyStyle, sheet, row, fields.size() - 1,"各项数据有效统计");
                //row = sheet.createRow(list.size()+2);
                //this.setTotal(fields, bodyStyle, row,1, list.size()+1);
            }
        }
        return ExcelPubUtil.getInputStreamIsSuccess(workbook, file);
    }


    private void annotate(CellStyle headerStyle, Sheet sheet,
                          Row row, int endColumn, String content) {
        Cell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, endColumn));
        // set type of content to cells
        cell.setCellType(CellType.STRING);
        headerStyle.setWrapText(true);
        // set header style
        cell.setCellStyle(headerStyle);
        row.setHeight((short) 500);
        // populate name into cell in first row
        cell.setCellValue(new HSSFRichTextString(content));

    }


    private void setTotal(List<Field> fields, CellStyle bodyStyle,
                          Row row, int start, int end) {
        for (Field field : fields) {
            // Set the entity class private property can be accessed
            field.setAccessible(true);
            Excel attr = field.getAnnotation(Excel.class);
            try {
                if (attr.isExport()) {
                    // create Excel cell
                    int columnNumber = attr.columnNum();
                    Cell cell = row.createCell(attr.columnNum());
                    cell.setCellStyle(bodyStyle);
                    if (attr.isSum()) {
                        String columnName = ExcelPubUtil.getColumnName(columnNumber + 1);
                        StringBuilder builder = new StringBuilder();
                        builder.append("SUM(").append(columnName);
                        builder.append(start).append(":").append(columnName);
                        builder.append(end).append(")");
                        cell.setCellType(CellType.STRING);
                        cell.setCellFormula(builder.toString());
                    }
                }
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            }
        }
    }
}
